IF OBJECT_ID('dbo.csp_rptMetric_RenewalsProcessedDetails') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptMetric_RenewalsProcessedDetails
END
GO
--csp_rptMetric_RenewalsProcessedDetails 'week', '1/11/2008', 185, '3/15/2009', 0, 0, null, null, 41359
CREATE PROCEDURE dbo.csp_rptMetric_RenewalsProcessedDetails
(
	@PeriodType varchar(50),
	@PeriodStartDate smalldatetime,
	@Institution int =  0,
	@PeriodEndDate smalldatetime = NULL,
	@Department INT = 0,
	@Unit varchar(1000) = '0',
 	@PrincipalInvestigator varchar(50)='0',
	@Sponsor  varchar(50) = '0',
	@DomainUserId int
)
AS
BEGIN

SELECT	DISTINCT 
				ai.Institution, 
				rp.ProposalNumber, 
				ai.PrincipalInvestigator  AS PIName, 
				ai.Department,
				rp.ChiefCode,
				ai.SponsorName AS Sponsor,
				ai.SponsorAgreementId as SponsorId,
				rp.ProposalStatus,
				rp.AwardPeriod,
				rp.AwardedBy,
				rp.AwardChangeDate,
				rp.UploadDate,
				rp.AwardStatus,
				rp.PostAwardManager
FROM  rptMetric_RenewalsProcessed  rp
INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec
		ON (sec.FolderNumber = rp.ProposalNumber)
INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
		ON ai.FolderNumber = rp.ProposalNumber	
WHERE rp.PeriodType = @PeriodType
		AND (ai.institutionId = @Institution OR @Institution =0 OR @Institution is NULL)
		AND (ai.DepartmentId = @Department OR @Department =0 OR @Department is NULL)
		AND (ai.UnitId in (Select * from dbo.fnSplitFundNumbers(@Unit)) OR @Unit='0' OR @unit is null or @unit='') 
		AND  rp.PeriodStartDate between @PeriodStartDate and IsNULL(@PeriodEndDate,@PeriodStartDate)
		AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator = '' OR @PrincipalInvestigator = '0')
		AND (ai.SponsorId = @Sponsor OR @Sponsor IS NULL OR @Sponsor = '' OR @Sponsor = '0')

END
	
GO

